//
//  SQLiteManger.swift
//  TCMHealth
//
//  Created by 刘占雨 on 2023/7/16.
//

import UIKit

class SQLiteManger: NSObject {
    // 创建单例
       public static let shareManger = SQLiteManger()
       
       // 数据库名称
       private let dbName = "ut.db"
        var searchResultArray: [String]?
       
       // 数据库地址
       lazy var dbURL: URL = {
           // 根据传入的数据库名称拼接数据库的路径
           let fileURL = try! FileManager.default
               .url(for: .applicationSupportDirectory, in: .userDomainMask,
                    appropriateFor: nil, create: true)
               .appendingPathComponent(dbName)
           print("数据库地址：", fileURL)
           return fileURL
       }()
        
       // FMDatabase对象（用于对数据库进行操作）
       lazy var db: FMDatabase = {
           let database = FMDatabase(url: dbURL)
           return database
       }()
        
       // FMDatabaseQueue对象（用于多线程事务处理）
       lazy var dbQueue: FMDatabaseQueue? = {
           // 根据路径返回数据库
           let databaseQueue = FMDatabaseQueue(url: dbURL)
           return databaseQueue
       }()
       
       
       // MARK: ---------------------- 以上为工具类内容，以下是工具类的方法示例 ----------------------
       
       
       // MARK: ---------------------- 表操作 ----------------------
       
       // 打开/创建数据库
       public func openDB() {
           let currentDB = FMDatabase(path: Bundle.main.path(forResource: "ut", ofType:"db")!)
           if currentDB.open() {
               print("数据库打开成功!")
           } else {
               print("数据库打开失败: \(currentDB.lastErrorMessage())")
           }
           currentDB.close()
       }
       
       // 创建数据表
       public func createTable() {
           // 编写SQL语句（id: 主键  name和age是字段名）
           let sql = "CREATE TABLE IF NOT EXISTS User( \n" +
               "id INTEGER PRIMARY KEY AUTOINCREMENT, \n" +
               "name TEXT, \n" +
               "age INTEGER \n" +
           "); \n"
           
           // 执行SQL语句（注意点: 在FMDB中除了查询以外, 都称之为更新）
           let currentDB = SQLiteManger.shareManger.db
           if currentDB.open() {
               if currentDB.executeUpdate(sql, withArgumentsIn: []){
                   print("创建表成功")
               }else{
                   print("创建表失败")
               }
           }
           currentDB.close()
       }
       
       // 删除数据表
       public func deleteTable() {
           // 编写SQL语句
           let sql = "DROP TABLE User;"
           
           // 执行SQL语句
           let currentDB = SQLiteManger.shareManger.db
           if currentDB.open() {
               if currentDB.executeUpdate(sql, withArgumentsIn: []){
                   print("删除表成功")
               }else{
                   print("删除表失败")
               }
           }
           currentDB.close()
       }
       
       // 添加表属性
       public func addTableVar() {
           // 编写SQL语句
           let sql = "ALTER TABLE User ADD phone varchar(256);"
           
           // 执行SQL语句
           let currentDB = SQLiteManger.shareManger.db
           if currentDB.open() {
               if currentDB.executeUpdate(sql, withArgumentsIn: []){
                   print("添加表字段成功")
               }else{
                   print("添加表字段失败")
               }
           }
           currentDB.close()
       }
       
       // MARK: ---------------------- 表数据操作 ----------------------
       
       // 表数据 插入
       public func insertData() {
           // 编写SQL语句
           let sql = "INSERT INTO User (name, age) VALUES ('hangge', 100);"
            
           // 执行SQL语句
           let currentDB = SQLiteManger.shareManger.db
           if currentDB.open() {
               if currentDB.executeUpdate(sql, withArgumentsIn: []){
                   print("插入成功")
               }else{
                   print("插入失败")
               }
           }
           currentDB.close()
       }
       
       // 表数据 插入(预编译)
       public func insertData2() {
           // 编写SQL语句
           let sql = "INSERT INTO User (name, age) VALUES (?,?);"
            
           // 执行SQL语句
           let currentDB = SQLiteManger.shareManger.db
           if currentDB.open() {
               if currentDB.executeUpdate(sql, withArgumentsIn: ["hangge", 100]){
                   print("插入成功")
               }else{
                   print("插入失败")
               }
           }
           currentDB.close()
       }
       
       // 表数据 更新
       public func updateData() {
           // 编写SQL语句
           let sql = "UPDATE User set name = 'hangge.com' WHERE id = 2;"
            
           // 执行SQL语句
           let currentDB = SQLiteManger.shareManger.db
           if currentDB.open() {
               if currentDB.executeUpdate(sql, withArgumentsIn: []){
                   print("更新成功")
               }else{
                   print("更新失败")
               }
           }
           currentDB.close()
       }
       
       // 表数据 更新(预编译)
       public func updateData2() {
           // 编写SQL语句
           let sql = "UPDATE User set name = ? WHERE id = ?;"
            
           // 执行SQL语句
           let currentDB = SQLiteManger.shareManger.db
           if currentDB.open() {
               if currentDB.executeUpdate(sql, withArgumentsIn: ["hangge.com", 1]){
                   print("更新成功")
               }else{
                   print("更新失败")
               }
           }
           currentDB.close()
       }
       
       // 表数据 删除
       public func deleteData() {
           // 编写SQL语句
           let sql = "DELETE FROM User WHERE id = 2;"
            
           // 执行SQL语句
           let currentDB = SQLiteManger.shareManger.db
           if currentDB.open() {
               if currentDB.executeUpdate(sql, withArgumentsIn: []){
                   print("删除成功")
               }else{
                   print("删除失败")
               }
           }
           currentDB.close()
       }
       
       // 表数据 删除(预编译)
       public func deleteData2() {
           // 编写SQL语句
           let sql = "DELETE FROM User WHERE id = ?;"
            
           // 执行SQL语句
           let currentDB = SQLiteManger.shareManger.db
           if currentDB.open() {
               if currentDB.executeUpdate(sql, withArgumentsIn: [3]){
                   print("删除成功")
               }else{
                   print("删除失败")
               }
           }
           currentDB.close()
       }
       
       // 表数据 查询
    public func queryMedicineData(medicineName: String,page: Int, sucess: (()->Void)) {
        if page == 1 {
            searchResultArray = Array()
        }
           // 编写SQL语句
           let sql = "select DISTINCT MEDICINE_TYPE_NAME,MEDICINE_ALIAS,EFFECT from SELF_OWNED_MEDICINE_VO WHERE MEDICINE_TYPE_NAME_PINYIN LIKE '%\(medicineName)%' LIMIT 10 OFFSET \((page - 1) * 10);"
            
           // 执行SQL语句
           let currentDB = FMDatabase(path: Bundle.main.path(forResource: "MedicineSearch", ofType:"db")!)
           if currentDB.open() {
               if let res = currentDB.executeQuery(sql, withArgumentsIn: []){
                   // 遍历输出结果
                   while res.next() {
                       let name = res.string(forColumn: "MEDICINE_TYPE_NAME") ?? ""
                       let alias = res.string(forColumn: "MEDICINE_ALIAS") ?? ""
                       let effect = res.string(forColumn: "EFFECT") ?? ""
                       let str = name + "," + alias + "," + effect
                       searchResultArray?.append(str)
                   }
               }else{
                   print("查询失败")
               }
           }
           sucess()
           currentDB.close()
       }
       
       // 表数据 查询(预编译)
       public func queryData2() {
           // 编写SQL语句
           let sql = "SELECT * FROM User WHERE id < ?"
            
           // 执行SQL语句
           let currentDB = SQLiteManger.shareManger.db
           if currentDB.open() {
               if let res = currentDB.executeQuery(sql, withArgumentsIn: [10]){
                   // 遍历输出结果
                   while res.next() {
                       let id = res.int(forColumn: "id")
                       let name = res.string(forColumn: "name")!
                       let age = res.int(forColumn: "age")
                       print(id, name, age)
                   }
               }else{
                   print("查询失败")
               }
           }
           currentDB.close()
       }
       
       // MARK: ---------------------- 表数据高级操作 ----------------------
       
       // 使用事务模式 插入多条数据
       public func queueInsertData() {
           // 使用事务插入10条数据
           if let queue = SQLiteManger.shareManger.dbQueue {
               queue.inTransaction { db, rollback in
                   do {
                       for i in 0..<10 {
                           try db.executeUpdate("INSERT INTO User (name, age) VALUES (?,?);",
                                                values: ["hangge", i])
                       }
                       print("插入成功!")
                   } catch {
                       print("插入失败，进行回滚!(之前插入的内容全部撤回)")
                       rollback.pointee = true
                   }
               }
           }
       }
}
